/****** Object:  StoredProcedure [dbo].[GetGamesThatNeedScores]    Script Date: 04/19/2007 23:22:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetGamesThatNeedScores] 
@UserID uniqueidentifier

AS
(
select g.GameID,g.LeagueID,g.gameday,g.hometeamID,g.awayteamID,g.gametime, home.teamID as YourTeamID,home.teamname as YourTeamName,away.TeamID as opponentteamID,away.teamName as OpponentteamNAme
from game g inner join 
	team home on home.teamID = g.hometeamID inner join
	team away on away.teamID = g.awayTeamID 
where g.gameDay < GetDate()
and
(
home.teamID in (select teamID 
		from
			teamplayer where UserId = @UserID and RoleID = 1)
)
and g.homehomeScore is null

union
select g.GameID,g.LeagueID,g.gameday,g.hometeamID,g.awayteamID,g.gametime, away.teamID as YourTeamID,away.teamname as YourTeamName,home.TeamID as opponentteamID,home.teamName as OpponentteamNAme
from game g inner join 
	team home on home.teamID = g.hometeamID inner join
	team away on away.teamID = g.awayTeamID 
where g.gameDay <= GetDate()
and
(
away.teamID in (select teamID 
		from
			teamplayer where UserId = @UserID and RoleID = 1)
)
and g.awayhomeScore is null
)
order by gameday 